package com.zxk28.notebook;

import java.io.File;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
import java.util.ResourceBundle;

import javax.sql.RowSet;

import org.eclipse.swt.SWT;
import org.eclipse.swt.events.MenuAdapter;
import org.eclipse.swt.events.MenuEvent;
import org.eclipse.swt.events.SelectionAdapter;
import org.eclipse.swt.events.SelectionEvent;
import org.eclipse.swt.graphics.Font;
import org.eclipse.swt.layout.FillLayout;
import org.eclipse.swt.widgets.Display;
import org.eclipse.swt.widgets.FileDialog;
import org.eclipse.swt.widgets.Menu;
import org.eclipse.swt.widgets.MenuItem;
import org.eclipse.swt.widgets.MessageBox;
import org.eclipse.swt.widgets.Shell;
import org.eclipse.swt.widgets.Table;
import org.eclipse.swt.widgets.TableColumn;
import org.eclipse.swt.widgets.TableItem;

import com.zxk28.sqlbuilders.SQLDeleteBuilder;
import com.zxk28.sqlbuilders.SQLInsertBuilder;
import com.zxk28.sqlbuilders.SQLSelectBuilder;
import com.zxk28.sqlbuilders.SQLUpdateBuilder;
import com.zxk28.sqlitehelper.SQLiteHelper;

public class Notebook {
	// resource
	private static ResourceBundle resourceBundle = ResourceBundle
			.getBundle("notebook");
	private static Font font = new Font(Display.getDefault(), resourceBundle.getString("Font_Name"), SWT.NORMAL, Integer.parseInt(resourceBundle.getString("Font_Size")));
	// shell fields
	private Shell shell = null;

	// fields
	private Table table = null;
	private SearchDialog searchDialog;

	// db file name
	private String notebook = null;

	private int lastSortColumn = -1;

	// constants

	private static final String CREATE_TABLE= "CREATE TABLE Notes(Id INTEGER PRIMARY KEY, Title TEXT, Content TEXT, LastUpdateTime TEXT);";
		
	private static final String[] columnNames = {
			resourceBundle.getString("Id"), resourceBundle.getString("Title"),
			resourceBundle.getString("Content"),
			resourceBundle.getString("Last_Update_Time") };

	public static void main(String[] args) {
		Display display = Display.getDefault();
		Notebook application = new Notebook();
		Shell shell = application.open(display);
		while (!shell.isDisposed()) {
			if (!display.readAndDispatch()) {
				display.sleep();
			}
		}
		display.dispose();
	}

	private Shell open(Display display) {
		shell = new Shell(display);
		shell.setLayout(new FillLayout());

		createMenu();

		table = new Table(shell, SWT.BORDER | SWT.MULTI | SWT.FULL_SELECTION | SWT.VIRTUAL | SWT.H_SCROLL | SWT.V_SCROLL);
		table.setHeaderVisible(true);
		table.setLinesVisible(true);
		table.setFont(font);
		table.setMenu(createPopUpMenu());
		table.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetDefaultSelected(SelectionEvent e) {
				TableItem[] items = table.getSelection();
				if (items.length > 0)
					editNote(items[0]);
			}
		});

		createTableColumn("Id", 150, true, 0);
		createTableColumn("Title", 150, true, 1);
		createTableColumn("Content", 500, false, 2);
		createTableColumn("Last_Update_Time", 150, true, 3);

		shell.setSize(table.computeSize(SWT.DEFAULT, SWT.DEFAULT).x, 350);
		shell.open();
		
		searchDialog = new SearchDialog(shell);
		
		return shell;
	}

	private Menu createPopUpMenu() {
		final Menu menu = new Menu(shell, SWT.POP_UP);
		menu.addMenuListener(new MenuAdapter() {
			@Override
			public void menuShown(MenuEvent e) {
				MenuItem[] items = menu.getItems();
				items[0].setEnabled(notebook != null);
				items[2].setEnabled(table.getSelectionCount() != 0);
				items[3].setEnabled(table.getSelectionCount() != 0);
				items[5].setEnabled(notebook != null);
			}
		});
		// menu >> New Note...
		MenuItem item  = new MenuItem(menu, SWT.NONE);
		item.setText(resourceBundle.getString("Pop_Up_New"));
		item.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				newNote();
			}
		});
		
		item = new MenuItem(menu, SWT.SEPARATOR);
		
		// menu >> Edit...
		item = new MenuItem(menu, SWT.NONE);
		item.setText(resourceBundle.getString("Pop_Up_Edit"));
		item.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				TableItem [] items = table.getSelection();
				if(items.length > 0)
					editNote(items[0]);
			}
		});
		
		// menu >> Delete...
		item = new MenuItem(menu, SWT.NONE);
		item.setText(resourceBundle.getString("Pop_Up_Delete"));
		item.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				TableItem [] items = table.getSelection();
				if(items.length > 0){
					MessageBox box = new MessageBox(shell, SWT.YES | SWT.NO);
					box.setMessage(resourceBundle.getString("ConfireToDelete"));
					
					if(box.open() == SWT.YES){
						deleteNote(items);
					}
				}
			}
		});
		
		item = new MenuItem(menu, SWT.SEPARATOR);
		
		// menu >> Find...
		item = new MenuItem(menu, SWT.NONE);
		item.setText(resourceBundle.getString("Pop_Up_Find"));
		item.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				find();
			}
		});
		
		return menu;
	}

	private void displayError(String msg){
		MessageBox box = new MessageBox(shell, SWT.ERROR);
		box.setMessage(msg);
		box.open();
		
	}
	
	private void createTableColumn(String resKey, int width,
			boolean enableSort, final int col) {
		final TableColumn column = new TableColumn(table, SWT.NONE);
		column.setText(resourceBundle.getString(resKey));
		column.setWidth(width);
		if (enableSort) {
			column.addSelectionListener(new SelectionAdapter() {
				@Override
				public void widgetSelected(SelectionEvent e) {
					sort(col);
				}
			});
		}
	}

	protected void sort(int col) {
		TableItem[] items = table.getItems();
		if(items.length <= 1)
			return;
		
		String[][] data = new String[items.length][table.getColumnCount()];
		
		for (int i = 0; i < data.length; i++) {
			for (int j = 0; j < data[i].length; j++) {
				data[i][j] = table.getItem(i).getText(j);
			}
		}
		
		Arrays.sort(data, new RowComparator(col));
		
		if(col != lastSortColumn){
			table.setSortColumn(table.getColumn(col));
			table.setSortDirection(SWT.UP);
			for (int i = 0; i < data.length; i++) {
				table.getItem(i).setText(data[i]);
			}
			lastSortColumn = col;
		}else{
			table.setSortDirection(SWT.DOWN);
			int j = data.length - 1;
			for (int i = 0; i < data.length; i++) {
				table.getItem(i).setText(data[j--]);
			}
			lastSortColumn = -1;
		}
	}
	
	protected void editNote(TableItem note) {
		DataEntryDialog dialog = new DataEntryDialog(shell);
		dialog .setLabels(columnNames);
		dialog.setValues(note.getText(0),note.getText(1),note.getText(2),note.getText(3));
		
		String [] lines = dialog.open();
		if(lines == null)
			return;
		
		String cmd = new SQLUpdateBuilder().setUpdateTable("Notes").setSetClause(
				"Title", lines[1]==null ? "" : lines[1],
						"Content", lines[2] == null ? "" : lines[2],
								"LastUpdateTime", lines[3] == null ? "" : lines[3]).setWhereClause("Id="+lines[0]).getCmd();
		
		try {
			SQLiteHelper.ExecuteUpdate(SQLiteHelper.SqliteUrlPrefix + notebook, cmd);
			note.setText(lines);
		} catch (SQLException e) {
			displayError(e.getMessage());
		}
		
	}

	private Menu createMenu() {
		Menu menuBar = new Menu(shell, SWT.BAR);
		shell.setMenuBar(menuBar);

		createFileMenu(menuBar);
		createSearchMenu(menuBar);
		createHelpMenu(menuBar);

		return menuBar;
	}

	private void createFileMenu(Menu menuBar) {
		// File
		MenuItem item = new MenuItem(menuBar, SWT.CASCADE);
		item.setText(resourceBundle.getString("File"));
		Menu menu = new Menu(shell, SWT.DROP_DOWN);
		item.setMenu(menu);
		// add the menu listener to set menu enabled or disabled
		menu.addMenuListener(new MenuAdapter() {
			@Override
			public void menuShown(MenuEvent e) {
				Menu menu = (Menu) e.widget;
				MenuItem[] item = menu.getItems(); 
				item[3].setEnabled(notebook != null); //net note
				item[4].setEnabled(table.getSelectionCount() != 0); // edit
				item[5].setEnabled(table.getSelectionCount() != 0); // delete
				item[7].setEnabled(notebook != null); //Save As
			}
		});

		// File >> New Notebook...
		MenuItem subItem = new MenuItem(menu, SWT.NONE);
		subItem.setText(resourceBundle.getString("New_Notebook"));
		subItem.setAccelerator('N');
		subItem.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				newNotebook();
			}
		});

		// File >> Load Notebook...
		subItem = new MenuItem(menu, SWT.NONE);
		subItem.setText(resourceBundle.getString("Load_Notebook"));
		subItem.setAccelerator('L');
		subItem.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				loadNotebook();
			}
		});

		subItem = new MenuItem(menu, SWT.SEPARATOR);

		// File >> New Note...
		subItem = new MenuItem(menu, SWT.NONE);
		subItem.setText(resourceBundle.getString("New_Note"));
		subItem.setAccelerator('A');
		subItem.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				newNote();
			}
		});

		// File >> Edit Note...
		subItem = new MenuItem(menu, SWT.NONE);
		subItem.setText(resourceBundle.getString("Modify_Note"));
		subItem.setAccelerator('E');
		subItem.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				TableItem[] items = table.getSelection();
				if(items.length > 0)
					editNote(items[0]);
			}
		});
		
		// File >> Delete Note
		subItem = new MenuItem(menu, SWT.NONE);
		subItem.setText(resourceBundle.getString("Delete_Note"));
		subItem.setAccelerator('D');
		subItem.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				TableItem [] items = table.getSelection();
				if(items.length > 0){
					MessageBox box = new MessageBox(shell, SWT.YES | SWT.NO);
					box.setMessage(resourceBundle.getString("ConfireToDelete"));
					
					if(box.open() == SWT.YES){
						deleteNote(items);
					}
				}
			}
		});
		
		subItem = new MenuItem(menu, SWT.SEPARATOR);

		// File >> Save As...
		subItem = new MenuItem(menu, SWT.NONE);
		subItem.setText(resourceBundle.getString("Save_As"));
		subItem.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				saveAs();
			}
		});

		// File >> Exit
		subItem = new MenuItem(menu, SWT.NONE);
		subItem.setText(resourceBundle.getString("Exit"));
		subItem.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				exit();
			}
		});
	}

	protected void exit() {
		shell.close();
	}

	protected void saveAs() {
		FileDialog fd = new FileDialog(shell, SWT.SAVE);
		fd.setFilterExtensions(new String[]{
				resourceBundle.getString("Notebook_Extension"), 
				"*.*"
				});
		fd.setFilterNames(new String[]{
				resourceBundle.getString("Notebook_Filter_Name") + " (" + resourceBundle.getString("Notebook_Extension") + ")", 
				resourceBundle.getString("All_Filter_Name") + " (*.*)"
				});
		
		if(fd.open() != null) {
			String beforeSaveAsNotebook = this.notebook;
			try {
				List<String> cmds = new ArrayList<>();
				
				String cmd = new SQLSelectBuilder().setColumns("*").setSelectTable("Notes").getCmd();
				RowSet rows = SQLiteHelper.ExecuteQuery(SQLiteHelper.SqliteUrlPrefix + notebook, cmd);
				
				if(rows.next()){
					do {
						String insertCmd = new SQLInsertBuilder()
						.setInsertTable("Notes")
						.setColumns("Id", "Title", "Content", "LastUpdateTime").setValues(
								rows.getLong(1),
								Utils.nullForValue(rows.getString(2), ""),
								Utils.nullForValue(rows.getString(3), ""),
								Utils.nullForValue(rows.getString(4), "")
								).getCmd();
						
						cmds.add(insertCmd);
						
					} while (rows.next());
				}
				
				//ready to insert the new file
				notebook = fd.getFilterPath() + File.separator + fd.getFileName();
				
				SQLiteHelper.ExecuteUpdate(SQLiteHelper.SqliteUrlPrefix + notebook, CREATE_TABLE);
				SQLiteHelper.ExecuteUpdate(SQLiteHelper.SqliteUrlPrefix + notebook, cmds.toArray(new String[]{}));

				shell.setText(resourceBundle.getString("Title_Bar") + notebook);
			} catch (SQLException e) {
				displayError(e.getMessage());
				notebook = beforeSaveAsNotebook;
			}
		}
	}

	protected void deleteNote(TableItem [] items) {
		
		Long[] ids = new Long[items.length];
		for(int i = 0; i < items.length; i++) {
			ids[i] = Long.parseLong(items[i].getText(0));
		}
		
		//delete from db
		String[] cmds = new String[ids.length];
		for (int i = 0; i < ids.length; i++) {
			cmds[i] = new SQLDeleteBuilder()
				.setDeleteTable("Notes")
				.setWhereClause("Id="+ids[i])
				.getCmd();
		}
		
		try {
			SQLiteHelper.ExecuteUpdate(SQLiteHelper.SqliteUrlPrefix + notebook, cmds);
			
			for (int i = 0; i < items.length; i++) {
				int index = table.indexOf(items[i]);
				table.remove(index);
			}
		} catch (SQLException e) {
			displayError(e.getMessage());
		}
	}

	protected void newNote() {
		
		if(notebook == null)
			return;
		
		DataEntryDialog dialog  = new DataEntryDialog(shell);
		dialog.setLabels(columnNames);
		String[] line = dialog.open();

		if(line == null) 
			return; //cancel
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
		long id = Long.parseLong(sdf.format(new Date()));
		
		String cmd = new SQLInsertBuilder().
				setInsertTable("Notes").
				setColumns("Id", "Title", "Content", "LastUpdateTime").
				setValues(
						id,
						Utils.nullForValue(line[1], ""),
						Utils.nullForValue(line[2], ""),
						Utils.nullForValue(line[3], "")
						).getCmd();
		
		try {
			SQLiteHelper.ExecuteUpdate(SQLiteHelper.SqliteUrlPrefix + notebook, cmd);

			line[0] = new Long(id).toString();
			TableItem item = new TableItem(table, SWT.NONE);
			item.setText(line);
			
		} catch (SQLException e) {
			displayError(e.getMessage());
		}
	}

	protected void newNotebook() {
		FileDialog fd = new FileDialog(shell, SWT.SAVE);
		fd.setFilterExtensions(new String[]{
				resourceBundle.getString("Notebook_Extension"), 
				"*.*"
				});
		fd.setFilterNames(new String[]{
				resourceBundle.getString("Notebook_Filter_Name")+" (" + resourceBundle.getString("Notebook_Extension") + ")", 
				resourceBundle.getString("All_Filter_Name") + " (*.*)"
				});
		
		if(fd.open() != null) {
			String beforeNewNotebook = this.notebook;
			
			notebook = fd.getFilterPath()+ File.separator+ fd.getFileName();
			try {
				SQLiteHelper.ExecuteUpdate(SQLiteHelper.SqliteUrlPrefix + notebook, CREATE_TABLE);
				table.removeAll();
				shell.setText(resourceBundle.getString("Title_Bar") + notebook);
			} catch (SQLException e) {
				displayError(e.getMessage());
				notebook = beforeNewNotebook;
			}
		}
	}

	protected void loadNotebook() {
		FileDialog fd = new FileDialog(shell);
		fd.setFilterExtensions(new String[]{
				resourceBundle.getString("Notebook_Extension"),
				"*.*"
				});
		fd.setFilterNames(new String[]{
				resourceBundle.getString("Notebook_Filter_Name"),
				resourceBundle.getString("All_Filter_Name")
				});
		if(fd.open() != null){
			String beforeLoadNotebook = this.notebook;
			
			notebook = fd.getFilterPath() + File.separator + fd.getFileName();
			
			try {
				List<String[]> data = readDataFromDB();
				
				table.removeAll();
				
				for (String[] line : data) {
					TableItem item = new TableItem(table, SWT.NONE);
					item.setText(line);
				}
				shell.setText(resourceBundle.getString("Title_Bar")+notebook);
			} catch (SQLException e) {
				displayError(e.getMessage());
				notebook = beforeLoadNotebook;
			}
		}
	}
	
	private List<String[]> readDataFromDB() throws SQLException{
		List<String[]> lines = new ArrayList<>();
		
		String cmd = new SQLSelectBuilder().setColumns("*").setSelectTable("Notes").getCmd();
		RowSet rows = SQLiteHelper.ExecuteQuery(SQLiteHelper.SqliteUrlPrefix+notebook,cmd);
		
		if(rows.next()){
			do {
				String[] line = new String[4];
				line[0] = rows.getLong(1)+"";
				line[1] = rows.getString(2) == null ? "" : rows.getString(2);
				line[2] = rows.getString(3) == null ? "" : rows.getString(3);
				line[3] = rows.getString(4) == null ? "" : rows.getString(4);
				
				lines.add(line);
				
			} while (rows.next());
		}
		
		return lines;
	}
	
	private void createSearchMenu(Menu menuBar) {
		// Search
		MenuItem item = new MenuItem(menuBar, SWT.CASCADE);
		item.setText(resourceBundle.getString("Search"));
		final Menu menu = new Menu(shell, SWT.DROP_DOWN);
		item.setMenu(menu);
		menu.addMenuListener(new MenuAdapter() {
			@Override
			public void menuShown(MenuEvent e) {
				MenuItem[] items = menu.getItems();
				items[0].setEnabled(notebook != null);
				items[1].setEnabled(notebook != null);
			}
		});
		
		// Search >> Find...
		MenuItem subItem  = new MenuItem(menu, SWT.NONE);
		subItem.setText(resourceBundle.getString("Find"));
		subItem.setAccelerator(SWT.MOD1+'F');
		subItem.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				find();
			}
		});
		
		// Search >> Find Next
		subItem = new MenuItem(menu, SWT.NONE);
		subItem.setText(resourceBundle.getString("Find_Next"));
		subItem.setAccelerator(SWT.F3);
		subItem.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				findNext();
			}
		});
		
	}

	protected void findNext() {
		// TODO Auto-generated method stub
		
	}

	protected void find() {
		searchDialog.open();
		
	}

	private void createHelpMenu(Menu menuBar) {
		MenuItem item  = new MenuItem(menuBar, SWT.CASCADE);
		item.setText(resourceBundle.getString("Help"));
		Menu menu = new Menu(shell, SWT.DROP_DOWN);
		item.setMenu(menu);
		
		// Help >> About
		MenuItem subItem = new MenuItem(menu, SWT.NONE);
		subItem.setText(resourceBundle.getString("About"));
		subItem.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				about();
			}
		});

	}

	protected void about() {
		MessageBox box = new MessageBox(shell);
		box.setMessage(resourceBundle.getString("About"));
		box.open();
	}

	private class RowComparator implements Comparator<String[]> {

		private int col;

		public RowComparator(int col) {
			this.col = col;
		}

		@Override
		public int compare(String[] row1, String[] row2) {
			return row1[col].compareTo(row2[col]);
		}
	}
}
